<?php
if ($_GET['id'] != 'neworacle') {
    die('');
}

function Terbilang($x) {
    $abil = array("", "satu", "dua", "tiga", "empat", "lima", "enam", "tujuh", "delapan", "sembilan", "sepuluh", "sebelas");
    if ($x < 12)
        return " " . $abil[$x];
    elseif ($x < 20)
        return Terbilang($x - 10) . "belas";
    elseif ($x < 100)
        return Terbilang($x / 10) . " puluh" . Terbilang($x % 10);
    elseif ($x < 200)
        return " seratus" . Terbilang($x - 100);
    elseif ($x < 1000)
        return Terbilang($x / 100) . " ratus" . Terbilang($x % 100);
    elseif ($x < 2000)
        return " seribu" . Terbilang($x - 1000);
    elseif ($x < 1000000)
        return Terbilang($x / 1000) . " ribu" . Terbilang($x % 1000);
    elseif ($x < 1000000000)
        return Terbilang($x / 1000000) . " juta" . Terbilang($x % 1000000);
}


function connect_db() {
    $conn = mysql_connect("localhost", "cvtarcom_root", "new7oracle");
    //opening connection
    if (!$conn) {
        die("Database connection failed : " . mysql_error());
    }
    //selecting db
    $select_db = mysql_select_db("cvtarcom_dbtm", $conn);
    if (!$select_db) {
        die("Database selection failed : " . mysql_error());
    }
}

function connect_db_local() {
    $conn = mysql_connect("localhost", "root", "");
    //opening connection
    if (!$conn) {
        die("Database connection failed : " . mysql_error());
    }
    //selecting db
    $select_db = mysql_select_db("tani_makmur", $conn);
    if (!$select_db) {
        die("Database selection failed : " . mysql_error());
    }
}

connect_db();

$jenis = $_GET['jenis'];

function getUrutanBP($idPengeluaran, $noDO) {
    $sql = "SELECT id_pengeluaran from pengeluaran_do where no_do = (select id_penebusan from penebusan where no_do = '".$noDO."' limit 1) order by id_pengeluaran";
    $result = mysql_query($sql);
    $urutan = 1;
    while($row = mysql_fetch_assoc($result)) {
        if ($row['id_pengeluaran'] == $idPengeluaran) {
            return $urutan;
        }
        $urutan++;
    }
    return $urutan;
}

if ($jenis == 'struk_petro') {
    $sql = "SELECT va.id_tagihan_kios, va.tanggal as tgl_salur_asli, va.kios, va.alamat_kios,va.kecamatan,va.kabupaten, va.berat_ton as ton_keluar, date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur, vp.* FROM v_pengeluaran_do vp join v_tagihan_all va on va.no_bp = vp.id_no_bp where va.no_kuitansi = '" . $_GET['no_kwitansi'] . "'";
    $noKW=$_GET['no_kwitansi'];
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)) {
        $urutanBP = getUrutanBP($row['id_no_bp'], $row['no_do']);
        $noKwitansiBaru = $urutanBP.substr($row['no_bp'], strpos($row['no_bp'], "/"), strlen($row['no_bp']));
        $tempDO=$row['no_do'];
        $tglSalurAsli = $row['tgl_salur_asli'];
        $idTagihan = $row['id_tagihan_kios'];
        $data = array(
                "tanggal_pengeluaran" => $row['tanggal_salur'],
                "pupuk" => $row['nama_lengkap'],
                "kios" => $row['kios'],
                "alamat_kios" => ($row['alamat_kios'].' Kec. '.$row['kecamatan']).' Kab. '.$row['kabupaten'],
                "kepala_gudang" => "BGR MAGETAN",
                "di" => "MAGETAN",
                "jumlah" => $row['ton_keluar'],
                "pupuk" => $row['nama_lengkap'],
                "no_do" => $row['no_do'],
                "no_bp" => $row['no_bp'],
                "tanggal_tebus" => $row['tanggal_do_keluar'],
                "jumlah_tebus" => $row['jumlah_tebus'],
                "alat_pengangkut" => $row['alat_pengangkut'],
                "nama_pengendara" => $row['nama_pengendara'],
                "no_polisi" => $row['no_polisi'],
                "no_kwitansi_baru" => $noKwitansiBaru
        );
    }
    //mengambil total penyaluran sebelum ini
    $query = "SELECT ifnull(sum(berat_ton),0) as ton from tagihan_kios where no_do=(select id_penebusan from penebusan where no_do='$tempDO') and no_kuitansi<>'".$noKW."' and tanggal <= '".$tglSalurAsli."' and id_tagihan_kios < ".$idTagihan;
    $result=mysql_query($query);
    $data1=array();
    while($row= mysql_fetch_assoc($result)) {
        $data1=array("no_do_sebelum"=>$row['ton']);
    }

    //mengambil total ton tiap no_do
    $query = "SELECT ifnull(jumlah_ton,0) as ton from penebusan where no_do='$tempDO'";
    $result=mysql_query($query);
    $data2=array();
    while($row= mysql_fetch_assoc($result)) {
        $data2=array("ton_do"=>$row['ton']);
    }
    $result=array_merge($data,$data2,$data1);
    echo json_encode($result);

} else if ($jenis == 'kwitansi_petro' || $jenis == 'kwitansi_kaltim' || $jenis == 'kwitansi_sriwijaya') {

    $data = $_GET['data'];
    $data = base64_decode($data);
    $tempData = json_decode($data);
    $arrData = array();
    foreach($tempData as $a => $b) {
        $arrData[$a] = $b;
    }

    $temp = explode('-', $arrData['kios']);
    $arrData['kios'] = str_replace(" ", "", $temp[1]);

    $sql = "SELECT * from m_data_pupuk";
    $result2 = mysql_query($sql);

    $data = array();

    $data["nama_kios"] = $temp[0];
    $jumlahUang = 0;

    $n = 1;

    while($rowPupuk = mysql_fetch_assoc($result2)) {
        $sql = "SELECT t.*, date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur FROM tagihan_kios t join v_tagihan_all va on va.id_tagihan_kios = t.id_tagihan_kios where t.pupuk = '".$rowPupuk['id_pupuk']."' and t.kios = '".$arrData['kios']."' and date_format(t.tanggal,'%Y-%m-%d') = '".$arrData['tanggal']."'";
        $result = mysql_query($sql);
        $totKgPupuk = 0;
        $hargaPupuk = 0;
        $total = 0;

        while($row = mysql_fetch_assoc($result)) {
            $tanggalSalur = $row['tanggal_salur'];
            $hargaPupuk = $row['harga_jual'] - $rowPupuk['potongan_kwitansi'];
            $totKgPupuk = $totKgPupuk + ($row['berat_ton'] * 1000);
            $jumlahUang = $jumlahUang + (($hargaPupuk)*$row['berat_ton']*1000);
        }

        $data["pupuk_".$n] = number_format($totKgPupuk) . " X " . number_format($hargaPupuk) . " = " . number_format($totKgPupuk * $hargaPupuk);
        $n++;
    }

    $data["jumlah_uang"] = number_format($jumlahUang);
    $data["tanggal_salur"] = $tanggalSalur;
    $data["jumlah_uang_terbilang"] = terbilang($jumlahUang);

    echo json_encode($data);

} else if ($jenis == 'surat_pengantar') {
    $sql = "SELECT date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur, va.kios, va.alamat_kios, va.berat_ton as ton_keluar, va.kecamatan, va.kabupaten, vp.* FROM v_pengeluaran_do vp join v_tagihan_all va on va.no_bp = vp.no_bp where va.no_kuitansi = '".$_GET['no_kwitansi']."'";
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)) {
        $urutanBP = getUrutanBP($row['id_no_bp'], $row['no_do']);
        $noKwitansiBaru = $urutanBP.substr($row['no_bp'], strpos($row['no_bp'], "/"), strlen($row['no_bp']));
        $data = array(
                "tanggal_salur" => $row['tanggal_salur'],
                "no_kwitansi_baru" => $noKwitansiBaru,
                "no_bp" => $row['no_bp'],
                "no_polisi" => $row['no_polisi'],
                "dari_gudang" => 'PT. BGR MAGETAN',
                "jumlah_tebus" => $row['jumlah_tebus'] . " Ton",
                "no_do" => $row['no_do'],
                "kios" => $row['kios'],
                "tanggal_tebus" => $row['tanggal_tebus'],
                "alamat_kios" => $row['alamat_kios'],
                "kecamatan" => 'Kec. '.$row['kecamatan'].' Kab. '.$row['kabupaten'],
                "pupuk" => $row['nama_lengkap'],
                "zak" => $row['ton_keluar'] * 20,
                "kg" => $row['ton_keluar'] * 1000,
                "alat_pengangkut" => $row['alat_pengangkut'],
                "nama_pengendara" => $row['nama_pengendara']
        );
        echo json_encode($data);
        break;
    }
} else if ($jenis == 'berita_acara') {
    $sql = "SELECT format(va.harga_jual,0) as harga_jual,date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur,format(va.jumlah_tagihan,0) as jumlah_tagihan, va.kios, va.alamat_kios, va.penanggung_jawab, va.alamat_kios, va.kecamatan, va.kabupaten, va.no_induk, va.berat_ton as berat, va.no_kuitansi as no_kuitansi, vp.* FROM v_pengeluaran_do vp join v_tagihan_all va on va.no_bp = vp.no_bp  where va.no_kuitansi = '".$_GET['no_kwitansi']."'";
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)) {
        $urutanBP = getUrutanBP($row['id_no_bp'], $row['no_do']);
        $noKwitansiBaru = $urutanBP.substr($row['no_bp'], strpos($row['no_bp'], "/"), strlen($row['no_bp']));
        $data = array(
                "no_kwitansi_baru" => $noKwitansiBaru,
                "pupuk" => $row['nama_lengkap'],
                "no_kuitansi" => $row['no_kuitansi'],
                "tanggal_salur" => $row['tanggal_salur'],
                "kios" => $row['kios'],
                "penanggung_jawab" => $row['penanggung_jawab'],
                "alamat_kios" => $row['alamat_kios'],
                "kecamatan" => $row['kecamatan'],
                "kabupaten" => $row['kabupaten'],
                "no_induk" => $row['no_induk'],
                "no_do" => $row['no_do'],
                "tanggal_do" => $row['tanggal_do_keluar'],
                "party" => $row['jumlah_tebus']. " Ton",
                "no_polisi" => $row['no_polisi'],
                "alat_pengangkut" => $row['alat_pengangkut'],
                "ton" => $row['berat'],
                "zak" => ($row['berat']*1000/ 50),
                "harga_jual" => $row['harga_jual'],
                "jumlah_tagihan" => $row['jumlah_tagihan'],
                "no_bp" => $row['no_bp']
        );
        echo json_encode($data);
        break;
    }
} else if ($jenis == 'kwitansi_angkutan') {

    $data = $_GET['data'];
    $data = base64_decode($data);
    $tempData = json_decode($data);
    $arrData = array();
    foreach($tempData as $a => $b) {
        $arrData[$a] = $b;
    }

    $temp = explode('-', $arrData['kios']);
    $arrData['kios'] = str_replace(" ", "", $temp[1]);

    $sql = "SELECT * from m_data_pupuk";
    $result2 = mysql_query($sql);

    $data = array();

    $data["nama_kios"] = $temp[0];
    $jumlahUang = 0;

    $n = 1;
    while($rowPupuk = mysql_fetch_assoc($result2)) {
        $sql = "SELECT t.*, date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur FROM tagihan_kios t join v_tagihan_all va on va.id_tagihan_kios = t.id_tagihan_kios where t.pupuk = '".$rowPupuk['id_pupuk']."' and t.kios = '".$arrData['kios']."' and date_format(t.tanggal,'%Y-%m-%d') = '".$arrData['tanggal']."'";
        $result = mysql_query($sql);
        $totKgPupuk = 0;
        $hargaPupuk = 0;
        $total = 0;

        while($row = mysql_fetch_assoc($result)) {
            $tanggalSalur = $row['tanggal_salur'];
            $hargaPupuk = $rowPupuk['potongan_kwitansi'];
            $totKgPupuk = $totKgPupuk + ($row['berat_ton'] * 1000);
            $jumlahUang = $jumlahUang + (($hargaPupuk)*$row['berat_ton']*1000);
        }

        $data["pupuk_".$n] = number_format($totKgPupuk) . " X " . number_format($hargaPupuk) . " = " . number_format($totKgPupuk * $hargaPupuk);
        $n++;
    }

    $data["jumlah_uang"] = number_format($jumlahUang);
    $data["tanggal_salur"] = $tanggalSalur;
    $data["jumlah_uang_terbilang"] = terbilang($jumlahUang);

    echo json_encode($data);

} else if (($jenis == 'tanda_terima_petro') || ($jenis == 'tanda_terima_sriwijaya') || ($jenis == 'tanda_terima_kaltim')) {
    $sql = "SELECT *, date_format(tanggal,'%d/%m/%Y') as tanggal_salur FROM v_tagihan_all where no_kuitansi = '".$_GET['no_kwitansi']."'";
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)) {
        $data = array(
                "tanggal_salur" => $row['tanggal_salur'],
                "no_kwitansi" => $row['no_kuitansi'],
                "diterima_dari" => $row['kios'],
                "jumlah_uang" => $row['jumlah_tagihan'],
                "pupuk" => $row['pupuk'],
                "berat_ton" => $row['berat_ton'],
                "terbilang" => terbilang($row['jumlah_tagihan']),
                "kios" => $row['kios']
        );
        echo json_encode($data);
        break;
    }
} else if ($jenis == 'struk_sriwijaya') {
    $sql = "SELECT va.id_tagihan_kios, va.tanggal as tgl_salur_asli, va.kios, va.alamat_kios,va.kecamatan,va.kabupaten, va.berat_ton as ton_keluar, date_format(va.tanggal,'%d/%m/%Y') as tanggal_salur, vp.* FROM v_pengeluaran_do vp join v_tagihan_all va on va.no_bp = vp.id_no_bp where va.no_kuitansi = '" . $_GET['no_kwitansi'] . "'";
    $noKW=$_GET['no_kwitansi'];
    $result = mysql_query($sql);
    while($row = mysql_fetch_assoc($result)) {
        $urutanBP = getUrutanBP($row['id_no_bp'], $row['no_do']);
        $noKwitansiBaru = $urutanBP.substr($row['no_bp'], strpos($row['no_bp'], "/"), strlen($row['no_bp']));
        $tempDO=$row['no_do'];
        $tglSalurAsli = $row['tgl_salur_asli'];
        $idTagihan = $row['id_tagihan_kios'];
        $data = array(
                "tanggal_pengeluaran" => $row['tanggal_salur'],
                "pupuk" => $row['nama_lengkap'],
                "kios" => $row['kios'],
                "alamat_kios" => ($row['alamat_kios'].' Kec. '.$row['kecamatan']).' Kab. '.$row['kabupaten'],
                "kepala_gudang" => "BGR MAGETAN",
                "di" => "MAGETAN",
                "jumlah" => ($row['ton_keluar']),
                "pupuk" => $row['nama_lengkap'],
                "no_do" => $row['no_do'],
                "no_bp" => $row['no_bp'],
                "tanggal_tebus" => $row['tanggal_do_keluar'],
                "jumlah_tebus" => ($row['jumlah_tebus']),
                "alat_pengangkut" => $row['alat_pengangkut'],
                "nama_pengendara" => $row['nama_pengendara'],
                "no_polisi" => $row['no_polisi'],
                "no_kwitansi_baru" => $noKwitansiBaru
        );
    }
    //mengambil total penyaluran sebelum ini
    $query = "SELECT ifnull(sum(berat_ton),0) as ton from tagihan_kios where no_do=(select id_penebusan from penebusan where no_do='$tempDO') and no_kuitansi<>'".$noKW."' and tanggal <= '".$tglSalurAsli."' and id_tagihan_kios < ".$idTagihan;
    $result=mysql_query($query);
    $data1=array();
    while($row= mysql_fetch_assoc($result)) {
        $data1=array("no_do_sebelum"=>($row['ton']));
    }

    //mengambil total ton tiap no_do
    $query = "SELECT ifnull(jumlah_ton,0) as ton from penebusan where no_do='$tempDO'";
    $result=mysql_query($query);
    $data2=array();
    while($row= mysql_fetch_assoc($result)) {
        $data2=array("ton_do"=>($row['ton']));
    }
    $result=array_merge($data,$data2,$data1);
    echo json_encode($result);
} else if ($jenis == 'list_kios') {
    $sql = "SELECT id_kios,nama FROM m_data_kios order by nama asc";
    $result = mysql_query($sql);
    $data = mysql_fetch_array($result);
    $data = array();
    $i = 0;
    while($row = mysql_fetch_assoc($result)) {
        $data[$i] = $row['nama']."-".$row['id_kios'];
        $i++;
    }
    echo json_encode($data);
} else if ($jenis == 'list_no_bp') {
    $sql = "SELECT no_bp,id_pengeluaran FROM pengeluaran_do order by id_pengeluaran desc limit 50";
    $result = mysql_query($sql);
    $data = mysql_fetch_array($result);
    $data = array();
    $i = 0;
    while ($row = mysql_fetch_assoc($result)) {
        $data[$i] = preg_replace('/[^(\x20-\x7F)]*/', '', $row['no_bp'] . "-" . $row['id_pengeluaran']);
        $i++;
    }
    echo json_encode($data);
} else if ($jenis == 'list_supir') {
    $sql = "SELECT id,nama_supir FROM m_data_supir order by nama_supir asc";
    $result = mysql_query($sql);
    $data = mysql_fetch_array($result);
    $data = array();
    $i = 0;
    while ($row = mysql_fetch_assoc($result)) {
        $data[$i] = $row['nama_supir'];
        $i++;
    }
    echo json_encode($data);
} else if ($jenis == 'list_no_do') {
    $sql = "SELECT no_do,id_penebusan FROM penebusan order by id_penebusan desc limit 50";

    $result = mysql_query($sql);
    $data = array();
    $i = 0;
    while (($row = mysql_fetch_array($result)) != null) {
        $data[$i] = preg_replace('/[^(\x20-\x7F)]*/', '', $row["no_do"] . "-" . $row['id_penebusan']);
        $i++;
    }
    echo json_encode($data);
} else if ($jenis == 'list_pupuk') {
    $sql = "SELECT id_pupuk,nama FROM m_data_pupuk order by id_pupuk asc";
    $result = mysql_query($sql);
    $data = mysql_fetch_array($result);
    $data = array();
    $i = 0;
    while ($row = mysql_fetch_assoc($result)) {
        $data[$i] = $row['nama'] . "-" . $row['id_pupuk'];
        $i++;
    }
    echo json_encode($data);
} else if ($jenis == 'list_kios_lengkap') {
    $sql = "SELECT * FROM m_data_kios where id_kios = '".$_GET['id_kios']."'";
    $result = mysql_query($sql);
    $row = mysql_fetch_array($result);
    $data['nama'] = $row['nama'];
    $data['alamat'] = $row['alamat'];
    $data['penanggung_jawab'] = $row['penanggung_jawab'];
    $data['kecamatan'] = $row['kecamatan'];
    $data['kabupaten'] = $row['kabupaten'];
    echo json_encode($data);
}

function convertTon($ton){
    return ($ton*20)." Zak (".($ton*100)." Kg)";
}
?>
